"""author: wenyao
   data: 2021/4/16
   project: devopscmdb
"""
#1、linux安装好mysql
#  yum install mariadb mariadb-server
#2、启动服务
#[root@sdyxz ~]# service mariadb start
#3、检查服务是否开始
# #[root@sdyxz ~]# ps -ef |grep mysql|grep -v grep
# mysql       9973       1  0 16:28 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr
#4、给mysql的root用户设定密码为123456
#[root@sdyxz ~]# mysqladmin -u root password 123456
#5、进入mysql  填写刚才设定的密码
# [root@sdyxz ~]# mysql -u root -p
# Enter password:
# Welcome to the MariaDB monitor.  Commands end with ; or \g.
# Your MariaDB connection id is 9
# Server version: 10.3.27-MariaDB MariaDB Server
#
# Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
#
# Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#
# MariaDB [(none)]>

# MariaDB [(none)]> show databases;   #查看当前mysql有哪些数据库

# MariaDB [(none)]> show databases;
# +--------------------+
# | Database           |
# +--------------------+
# | information_schema |
# | mysql              |
# | performance_schema |
# +--------------------+
# 3 rows in set (0.002 sec)
#
# MariaDB [(none)]> use mysql;
# Reading table information for completion of table and column names
# You can turn off this feature to get a quicker startup with -A
#
# Database changed
# MariaDB [mysql]> show tables;
# MariaDB [mysql]> select * from user \G; #查看user表的内容
# MariaDB [mysql]> update user set Host='%' where User="root" and Host="localhost";
# #修改user表的内容，将User为root，Host为localhost的那条字段的Host值修改为%
#  允许所有主机访问
# MariaDB [mysql]> select * from user \G;
# *************************** 1. row ***************************
#                   Host: %
#                   User: root
#               Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
#
#创建数据库sc
# MariaDB [mysql]> create database sc;
# Query OK, 1 row affected (0.001 sec)
#
# MariaDB [mysql]> show databases;
# +--------------------+
# | Database           |
# +--------------------+
# | information_schema |
# | mysql              |
# | performance_schema |
# | sc                 |
# +--------------------+
# 4 rows in set (0.001 sec)
# MariaDB [mysql]> use sc;
# Database changed
#############创建表格userinfo
# create table userinfo(
#   username  varchar(128),
#   userpasswd varchar(128)
# );
# #
# MariaDB [sc]> create table userinfo(
#     ->   username  varchar(128),
#     ->   userpasswd varchar(128)
#     -> );
# Query OK, 0 rows affected (0.012 sec)


# MariaDB [sc]> show tables;
# +--------------+
# | Tables_in_sc |
# +--------------+
# | userinfo     |
# +--------------+
# 1 row in set (0.001 sec)

# MariaDB [sc]> insert into userinfo(username,userpasswd) values("wen2","123456");
# Query OK, 1 row affected (0.006 sec)
#
# MariaDB [sc]> insert into userinfo(username,userpasswd) values("wen3","123456");
# Query OK, 1 row affected (0.006 sec)
#
# MariaDB [sc]> select * from userinfo;
# +----------+------------+
# | username | userpasswd |
# +----------+------------+
# | wen      | 123456     |
# | wen2     | 123456     |
# | wen3     | 123456     |
# +----------+------------+
# 3 rows in set (0.000 sec)

import pymysql
#连接数据库
#conncet函数参数，     mysql的主机ip    mysql用户 密码     数据库
db = pymysql.connect("192.168.0.177", "root", "123456", "sc")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print("Database version : %s " % data)

# 关闭数据库连接
db.close()
